﻿using SpreadsheetGear;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.DataLayer.Shared;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sustainalytics.HistoricalData.Importer
{

    class Program
    {

        private const int weightSheetIndex = 2;
        private const int rawScoresSheetIndex = 3;
        
        private const int reportHeaderRowIndex = 11;

        private const int companyCapitalIQIDColumnIndex = 9;
        private const int companyIndicatorStartColumnIndex = 23;
        private const int companyIndicatorEndColumnIndex = 182;

        
        static void Main(string[] args)
        {

            string strConnectionString = ConfigurationManager.ConnectionStrings["SqlDB_RP_ConnectionString"].ConnectionString;

//            string strQuery = @"SELECT TOP 1 
//	                           [ArchivingId]
//	                          ,ad.[Date]
//                              ,ac.[ArchivingDateId]
//                              ,[ReportTypeId]
//                              ,[ReportDescription]
//                              ,[CompanyId]
//                              ,[RenderingStart]
//                              ,[RenderingEnd]
//                              ,[ReportStream]
//                          FROM [dbo].[ArchivingCatalogs] ac
//                          INNER JOIN [dbo].[ArchivingDates] ad ON ad.ArchivingDateId = ac.ArchivingDateId
//                          WHERE 
//                          ad.Completed = 1 
//                          AND ac.ReportTypeId = 2
//                          ORDER BY ad.[Date] DESC";

            string strQuery = @"SELECT TOP 1
	                           [ArchivingId]
	                          ,ad.[Date]
                              ,ac.[ArchivingDateId]
                              ,[ReportTypeId]
                              ,[ReportDescription]
                              ,[CompanyId]
                              ,[RenderingStart]
                              ,[RenderingEnd]
                              ,[ReportStream]
                              FROM [dbo].[ArchivingCatalogs] ac
                              INNER JOIN [dbo].[ArchivingDates] ad ON ad.ArchivingDateId = ac.ArchivingDateId
                              WHERE 
                              ad.Completed = 1 
                              AND ac.ReportTypeId = 2
                              AND ad.[Date] >= CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime)
                              AND ad.[Date] <= CAST('31 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime)
                              ORDER BY ad.[Date] DESC";


            using (SqlConnection connection = new SqlConnection(strConnectionString))
            {
                SqlCommand command = new SqlCommand(strQuery, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(String.Format("Processing {0} from {1} ...", reader["ReportDescription"] , reader["Date"].ToString()));

                        var relativeDataReport = Factory.GetWorkbookSet().Workbooks.OpenFromMemory((byte[])reader["ReportStream"]);

                        //import to mongo database: ClientDW , collection: CompanyHistoricalDataIndicators
                        ImportDataFromExcelReport(relativeDataReport);

                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }

            Console.ReadLine();

        }


        public static void ImportDataFromExcelReport(IWorkbook book)
        {
            var _repHistData = new RepositoryCollection<CompanyHistoricalDataIndicators>(ConfigurationManager.ConnectionStrings["MongoDB_ClientDWPlatform_ConnectionString"].ConnectionString);
            var clientDWRepository = ClientDwRepository.GetInstance(ConfigurationManager.ConnectionStrings["MongoDB_ClientDWPlatform_ConnectionString"].ConnectionString);


            var weightsSheet = book.Worksheets[weightSheetIndex];
            var rawScoresSheet = book.Worksheets[rawScoresSheetIndex];


            //get weights and raw scores
            for (int rowNumber = reportHeaderRowIndex + 1; ;rowNumber++)
            {

                if (weightsSheet.Cells[rowNumber, companyCapitalIQIDColumnIndex].Value == null || 
                    rawScoresSheet.Cells[rowNumber, companyCapitalIQIDColumnIndex].Value == null)
                    break;

                string cIQID = weightsSheet.Cells[rowNumber, companyCapitalIQIDColumnIndex].Value.ToString();
                CompanyHistoricalDataIndicators hdi = new CompanyHistoricalDataIndicators() { CapitalIQID = cIQID };
                var corpData = clientDWRepository.GetCorporateDataByCapitalIQID(hdi.CapitalIQID);

                if (corpData != null)
                {
                    Console.WriteLine(string.Format("Importing weights and raw scores for {0} ...", corpData.CompanyName));

                    hdi.Id = corpData.Id;

                    var indicators = new List<IndicatorData>();

                    for (int indIndex = companyIndicatorStartColumnIndex; indIndex <= companyIndicatorEndColumnIndex; indIndex++)
                    {

                        var indData = new IndicatorData();
                        indData.IndicatorNumber = weightsSheet.Cells[reportHeaderRowIndex, indIndex].Value.ToString().Split('\n')[0];
                        double weight;
                        if (double.TryParse(weightsSheet.Cells[rowNumber, indIndex].Value.ToString(), out weight))
                        {
                            indData.Weight = weight;
                        }
                        int rawScore;
                        if (int.TryParse(rawScoresSheet.Cells[rowNumber, indIndex].Value.ToString(), out rawScore))
                        {
                            indData.RawScore = rawScore;
                        }

                        indicators.Add(indData);
                    }

                    hdi.Indicators = indicators;

                    _repHistData.Update(hdi.Id, hdi);
                }
                else 
                {
                    Console.WriteLine(string.Format("Company with CapitalIQID {0} was not found in corporate data ", weightsSheet.Cells[rowNumber, companyCapitalIQIDColumnIndex].Value.ToString())); 
                }
            }


        }
    }
}
